<?php
class Command {
    protected $prefix = 'me_';
    protected $table = 'order';
    protected $alias = 'o';
    protected $join = '';
    protected $column = '';
    protected $where = '';
    protected $query = [];
    public function alias($arg){
        $this->alias = $arg;
        return $this;
    }
    public function join($join){
        $type_array = ['LEFT','RIGHT','FULL','INNER'];
        // $join_array = array(
        //     'left' => 'LEFT',
        //     'right' => 'RIGHT',
        //     'full' => 'FULL',
        //     'inner' => 'INNER',
        // );
        $joins = '';
        foreach($join as $sub_table => $relation){
            var_dump($relation);
            $table = $this->prefix.$sub_table;
            $type = $relation[0];
            $column = $relation[1];
            if(is_string($column)){
                $columns = 'USING ( ' . $column . ' )';
                $joins .=  ' ' . $type_array[$type] . ' JOIN ' . $table .' ON '. $columns;

                var_dump($relation);
            }else{
                foreach ($column as $key => $value){
                    if(strpos($key,'.') > 0){
                        // For ['tableB.column' => 'column']
                        if(isset($relation[2])){
                            $columns = $key;
                            $alias = $table . ' AS '.$relation[2];
                            $temp = ' ' . $type_array[$type] . ' JOIN ' . $alias .' ON '. $columns;
                        }else{
                            // preg_match('/(^[a-zA-Z_.+-]+)@([a-zA-Z_-]+).([a-zA-Z]{2,4}$)/i', "jeff@nowhere.com", $matches);
                            preg_match('/(^[a-zA-Z0-9_]+).([a-zA-Z0-9_]+)/i', $key, $match);
                            var_dump($match);
                            if(isset($match[1]) && $match[1] == $sub_table){
                                $columns = $this->prefix.$key;
                                $temp = ' ' . $type_array[$type] . ' JOIN ' . $table .' ON'. $columns;
                            }else{
                                $columns = $key;
                                $alias = $table . ' AS '.$relation[2];
                                $temp = ' ' . $type_array[$type] . ' JOIN ' . $alias .' ON '. $columns;
                            }
                        }                                
                    }else{
                        // For ['column1' => 'column2']
                        $columns =  $table . '.' . $key.'';
                        $temp = ' ' . $type_array[$type] . ' JOIN ' . $table .' ON '. $columns;
                    }
                    preg_match('/(^[a-zA-Z0-9_]+).([a-zA-Z0-9_]+)/i', $value, $match);
                    if(isset($match[1]) && $match[1] == $this->alias){
                        $item = $value;
                    }else{
                        $item = $this->prefix.$this->table . '.' . $value;
                            
                    }
                    $joins .= $temp . '=' . $item  . ' ';
                    // $joins[] =  ' ' . $type_array[$type] . ' JOIN ' . $table . $columns;
                    }
                }
            }

        }
        // var_dump($joins);
        // $result = 'ON ' . implode($joins, ' AND ');
        // $this->join .= ' ' . $type_array[$type] . ' JOIN ' . $table . $result ;
        $this->join = $joins;
        return $this;
    }
    public function where($where){
        $where_clause = '';
        if (is_array($where)){
            $where_keys = array_keys($where);
            $where_AND = preg_grep("/^AND\s*#?$/i", $where_keys);
            $where_OR = preg_grep("/^OR\s*#?$/i", $where_keys);

            $single_condition = array_diff_key($where, array_flip(
                array('AND', 'OR', 'GROUP', 'ORDER', 'HAVING', 'LIMIT', 'LIKE', 'MATCH')
            ));

            if ($single_condition != array()){
                $condition = $this->data_implode($single_condition, '');

                if ($condition != '')
                {
                    $where_clause = ' WHERE ' . $condition;
                }
            }

            if (!empty($where_AND)){
                $value = array_values($where_AND);
                $where_clause = ' WHERE ' . $this->data_implode($where[ $value[ 0 ] ], ' AND');
            }
            if (!empty($where_OR)){
                $value = array_values($where_OR);
                $where_clause = ' WHERE ' . $this->data_implode($where[ $value[ 0 ] ], ' OR');
            }
            if (isset($where[ 'MATCH' ])){
                $MATCH = $where[ 'MATCH' ];
                if (is_array($MATCH) && isset($MATCH[ 'columns' ], $MATCH[ 'keyword' ])){
                    $where_clause .= ($where_clause != '' ? ' AND ' : ' WHERE ') . ' MATCH (' . str_replace('.', '.', implode($MATCH[ 'columns' ], ', ')) . ') AGAINST (' . $this->db->quote($MATCH[ 'keyword' ]) . ')';
                }
            }

            if (isset($where[ 'GROUP' ])){
                $where_clause .= ' GROUP BY ' . $this->column_quote($where[ 'GROUP' ]);
                if (isset($where[ 'HAVING' ])){
                    $where_clause .= ' HAVING ' . $this->data_implode($where[ 'HAVING' ], ' AND');
                }
            }

            if (isset($where[ 'ORDER' ])){
                $ORDER = $where[ 'ORDER' ];

                if (is_array($ORDER)){
                    $stack = array();

                    foreach ($ORDER as $column => $value){
                        if (is_array($value)){
                            $stack[] = 'FIELD(' . $this->column_quote($column) . ', ' . $this->array_quote($value) . ')';
                        }else if ($value === 'ASC' || $value === 'DESC'){
                            $stack[] = $this->column_quote($column) . ' ' . $value;
                        }else if (is_int($column)){
                            $stack[] = $this->column_quote($value);
                        }
                    }
                    $where_clause .= ' ORDER BY ' . implode($stack, ',');
                }else{
                    $where_clause .= ' ORDER BY ' . $this->column_quote($ORDER);
                }
            }

            if (isset($where[ 'LIMIT' ])){
                $LIMIT = $where[ 'LIMIT' ];
                if (is_numeric($LIMIT)){
                    $where_clause .= ' LIMIT ' . $LIMIT;
                }

                if (
                    is_array($LIMIT) &&
                    is_numeric($LIMIT[ 0 ]) &&
                    is_numeric($LIMIT[ 1 ])
                )
                {
                    if ($this->database_type === 'pgsql')
                    {
                        $where_clause .= ' OFFSET ' . $LIMIT[ 0 ] . ' LIMIT ' . $LIMIT[ 1 ];
                    }
                    else
                    {
                        $where_clause .= ' LIMIT ' . $LIMIT[ 0 ] . ',' . $LIMIT[ 1 ];
                    }
                }
            }
        }
        else
        {
            if ($where != null)
            {
                $where_clause .= ' ' . $where;
            }
        }
        $this->where = $where_clause;
        return $this;
        // var_dump($where_clause);
        // return $where_clause;
    }
    protected function data_implode($data, $conjunctor, $outer_conjunctor = null){
        $wheres = array();
        foreach ($data as $key => $value){
            $type = gettype($value);
            if (preg_match("/^(AND|OR)(\s+#.*)?$/i", $key, $relation_match) && $type == 'array'){
                $wheres[] = 0 !== count(array_diff_key($value, array_keys(array_keys($value)))) ?
                          '(' . $this->data_implode($value, ' ' . $relation_match[ 1 ]) . ')' :
                          '(' . $this->inner_conjunct($value, ' ' . $relation_match[ 1 ], $conjunctor) . ')';
            }else{
                preg_match('/(#?)([\w\.\-]+)(\[(\>|\>\=|\<|\<\=|\!|\<\>|\>\<|\!?~)\])?/i', $key, $match);
                $column = $this->column_quote($match[ 2 ]);
                if (isset($match[ 4 ])){
                    $operator = $match[ 4 ];
                    if ($operator == '!'){
                        switch ($type){
                        case 'NULL':
                            $wheres[] = $column . ' IS NOT NULL';
                            break;

                        case 'array':
                            $wheres[] = $column . ' NOT IN (' . $this->array_quote($value) . ')';
                            break;

                        case 'integer':
                        case 'double':
                            $wheres[] = $column . ' != ' . $value;
                            break;

                        case 'boolean':
                            $wheres[] = $column . ' != ' . ($value ? '1' : '0');
                            break;

                        case 'string':
                            $wheres[] = $column . ' != ' . $this->fn_quote($key, $value);
                            break;
                        }
                    }

                    if ($operator == '<>' || $operator == '><'){
                        if ($type == 'array'){
                            if ($operator == '><'){
                                $column .= ' NOT';
                            }

                            if (is_numeric($value[ 0 ]) && is_numeric($value[ 1 ])){
                                $wheres[] = '(' . $column . ' BETWEEN ' . $value[ 0 ] . ' AND ' . $value[ 1 ] . ')';
                            }else{
                                $wheres[] = '(' . $column . ' BETWEEN ' . $this->db->quote($value[ 0 ]) . ' AND ' . $this->db->quote($value[ 1 ]) . ')';
                            }
                        }
                    }

                    if ($operator == '~' || $operator == '!~'){
                        if ($type != 'array'){
                            $value = array($value);
                        }
                        $like_clauses = array();
                        foreach ($value as $item){
                            $item = strval($item);
                            if (preg_match('/^(?!(%|\[|_])).+(?<!(%|\]|_))$/', $item)){
                                $item = '%' . $item . '%';
                            }
                            $like_clauses[] = $column . ($operator === '!~' ? ' NOT' : '') . ' LIKE ' . $this->fn_quote($key, $item);
                        }
                        $wheres[] = implode(' OR ', $like_clauses);
                    }

                    if (in_array($operator, array('>', '>=', '<', '<='))){
                        $condition = $column . ' ' . $operator . ' ';

                        if (is_numeric($value))
                        {
                            $condition .= $value;
                        }
                        elseif (strpos($key, '#') === 0)
                        {
                            $condition .= $this->fn_quote($key, $value);
                        }
                        else
                        {
                            $condition .= $this->db->quote($value);
                        }

                        $wheres[] = $condition;
                    }
                }
                else
                {
                    switch ($type)
                    {
                    case 'NULL':
                        $wheres[] = $column . ' IS NULL';
                        break;

                    case 'array':
                        $wheres[] = $column . ' IN (' . $this->array_quote($value) . ')';
                        break;

                    case 'integer':
                    case 'double':
                        $wheres[] = $column . ' = ' . $value;
                        break;

                    case 'boolean':
                        $wheres[] = $column . ' = ' . ($value ? '1' : '0');
                        break;

                    case 'string':
                        $wheres[] = $column . ' = ' . $this->fn_quote($key, $value);
                        break;
                    }
                }
            }
        }

        return implode($conjunctor . ' ', $wheres);
    }

    protected function column_quote($string){
        // preg_match('/(\(JSON\)\s*|^#)?([a-zA-Z0-9_]*)\.([a-zA-Z0-9_]*)/', $string, $column_match);
        preg_match('/(\(JSON\)\s*|^#)?([a-zA-Z0-9_]*)\.([a-zA-Z0-9\*_]*)/', $string, $column_match);
        // var_dump($column_match);
        if (isset($column_match[ 2 ], $column_match[ 3 ])){
            if($column_match[3] == '*'){
                $table = $this->prefix.$column_match[2];
                $fields = $this->db->getFields($this->prefix.$column_match[2]);
                $callback = function ($var) use ($table){
                    return '`' . $table . '`.`' .$var.'`';
                };

                $fields_tmp = array_map($callback,$fields);
                $fields_result = implode($fields_tmp, ',');
                return $fields_result;
            }else{
                return '`' . $this->prefix . $column_match[ 2 ] . '`.`' . $column_match[ 3 ] . '`';
            }

        }else{
            return  '`'.$string.'`';
        }
    }
    public function column(&$columns){
        $stack = [];
        foreach ($columns as $key => $value){
            if (is_array($value)){
                if(!is_int($key)){
                    foreach($value as $v){
                        $stack[] = '`' . $key . '`.`' . $v . '`';
                    }
                }else{
                    $stack[] = $this->column($value);
                }
            }else{
                preg_match('/([a-zA-Z0-9_\-\.]+)\s*\(([a-zA-Z0-9_\-]*)\)/i', $value, $match);
                if (isset($match[ 1 ], $match[ 2 ])){
                    $stack[] = $this->column_quote( $match[ 1 ]) . ' AS ' . $this->column_quote( $match[ 2 ]);
                    $columns[ $key ] = $match[ 2 ];
                }else{
                    $stack[] = $this->column_quote( $value);
                }
            }
        }
        // var_dump($stack);
        $this->column = implode($stack, ',');
        // var_dump($this->column);
        return $this;
    }
    public function select(){
        $query = 'SELECT ';
        if(isset($this->column)){
            if(isset($this->alias)){
                $query = $query . $this->column . ' FROM '.$this->prefix.$this->table . ' AS ' . $this->alias;
            }else{
                $query = $query . $this->column . ' FROM '.$this->prefix.$this->table;
            }
        }else{
            
        }
        if(isset($this->join)){
            $query = $query . $this->join;
        }
        
        if(isset($this->where)){
            $query = $query . $this->where;
            
        }
        var_dump($query);
    }
}